package com.hsnn.medstgmini.pro.project.dao.impl;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.model.DicExperttype;
import com.hsnn.medstgmini.base.dict.model.DicSys;
import com.hsnn.medstgmini.base.std.model.StdExpert;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.project.dao.BidExpertExtractRuleDao;
import com.hsnn.medstgmini.pro.project.model.BidExpertExtractListRuleList;
import com.hsnn.medstgmini.pro.project.model.BidExpertExtractRule;
import com.hsnn.medstgmini.pro.project.model.BidExpertExtractRuleListFormLog;
import com.hsnn.medstgmini.pro.project.model.BidExpertType;
import com.hsnn.medstgmini.pro.project.model.BidProjExpert;
import com.hsnn.medstgmini.pro.project.model.form.BidExpertExtractRuleListForm;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.ExpertExtract;
import com.hsnn.medstgmini.util.Pagination;

@Repository
public class BidExpertExtractRuleDaoImpl implements BidExpertExtractRuleDao{
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	@Autowired
	private ExpertExtract expertExtract;
	
	@Autowired
	private HibernateSqlUtil hibernateUtil;
	
	@Autowired
	private HibernateSqlUtil dao;
	
	/////////////////////////////////////////////////////////////////////////////////////////////////////////
	/**
	 * @category 根据id获取专家类型
	 * @author 徐国鹏
	 * @param String
	 * @return BidExpertExtractRuleList
	 */
	@Override
	public Object getExpertTypeRuleById() {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(*) from bid_expert_type ");
		Query query = session.createSQLQuery(sql.toString());
		return query.uniqueResult();
		
	}
	/**
	 * @category 根据id抽取规则
	 * @author 徐国鹏
	 * @param String
	 * @return BidExpertExtractRuleList
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getRuleByIdList(String ruleId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select rule_id as ruleId,  proj_id as projId,  max_extract_num as maxExtractNum,  infusion as infusion,  exec_status as execStatus, ");
		sql.append(" exec_user as execUser,  DATE_FORMAT(exec_datetime,'%Y-%m-%d %T') as execDatetime,  extract_num as extractNum,  status as status,  remarks as remarks, ");
		sql.append(" DATE_FORMAT(upd_datetime,'%Y-%m-%d %T') as updDatetime,  upd_user as updUser  ");
		sql.append(" from bid_expert_extract_rule where rule_id = :ruleId   ");
		Query query = session.createSQLQuery(sql.toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setParameter("ruleId", ruleId);
		return  (List<Map<String, Object>>) query.list();
	}
	/**
	 * @category 根据id获取医疗机构
	 * @author 徐国鹏
	 * @param String
	 * @return BidExpertExtractRuleList
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getHospRuleByIdList(String ruleId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select rule_hosp_type_id as ruleHospTypeId, rule_id as ruleId, hosp_type_id as hospTypeId, expert_rate as expertRate  ");
		sql.append(" from rule_hosp_type where rule_id = :ruleId   ");
		Query query = session.createSQLQuery(sql.toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setParameter("ruleId", ruleId);
		return  (List<Map<String, Object>>) query.list();
	}
	/**
	 * @category 根据id获取专家类型
	 * @author 徐国鹏
	 * @param String
	 * @return BidExpertExtractRuleList
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getExpertRuleByIdList(String ruleId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select bid_expert_type_id as bidExpertTypeId, rule_id as ruleId, expert_type_id as expertTypeId, count as count ");
		sql.append("  from bid_expert_type where rule_id = :ruleId  ");
		Query query = session.createSQLQuery(sql.toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setParameter("ruleId", ruleId);
		return  (List<Map<String, Object>>) query.list();
	}
	
	/**
	 * @category 获取一级专家类别列表
	 * @author 徐国鹏
	 * @param fatherRuleId
	 * @return List<BidExpertExtractRule> list
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<BidExpertExtractListRuleList> getSuExpertTypeByInfusion(String fatherExpertId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.* FROM dic_experttype A WHERE A.father_experttype_id = :fatherExpertId");
		Query query = session.createSQLQuery(sql.toString()).addEntity(DicExperttype.class);
		query.setParameter("fatherExpertId", fatherExpertId);
		return query.list();
	}
	
	/**
	 * @category 保存规则
	 * @author 徐国鹏
	 * @return RuleType
	 */
	@Override
	public void saveRuleByIdList(BidExpertExtractRuleListForm data) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		if(data.getType()==1){
			//update
			sql.append(" update bid_expert_extract_rule set max_extract_num = :maxExtractNum,infusion = :infusion,upd_datetime = :updDatetime,upd_user = :updUser where rule_id = :ruleId ");
			Query query = session.createSQLQuery(sql.toString());
			query.setString("ruleId",data.getRuleId());
			query.setString("maxExtractNum", data.getMaxExtractNum());
			query.setString("infusion", data.getInfusion());
			query.setTimestamp("updDatetime", DateUtil.getTimeStamp(new Date()));
			query.setString("updUser", data.getUpdUser());
			query.executeUpdate();
			session.flush();
			writeLog(data);
		}else{
			//新增
			sql.append(" insert into bid_expert_extract_rule (rule_id,proj_id,rule_code,max_extract_num,infusion,exec_status,status,upd_datetime,upd_user) ");
			sql.append(" values(:ruleId,:projId,(select rule_code+1 from bid_project_details where proj_id = :projId),:maxExtractNum,:infusion,0,1,:updDatetime,:updUser) ");
			Query query = session.createSQLQuery(sql.toString());
			query.setString("ruleId", data.getRuleId());
			query.setString("projId", data.getProjId());
			query.setString("maxExtractNum", data.getMaxExtractNum());
			query.setString("infusion", data.getInfusion());
			query.setTimestamp("updDatetime", DateUtil.getTimeStamp(new Date()));
			query.setString("updUser", data.getUpdUser());
			query.executeUpdate();
			session.flush();
			writeLog(data);
			StringBuffer sql1 = new StringBuffer();
			sql1.append(" update bid_project_details set rule_code = rule_code+1 ");
			sql1.append(" where proj_id = :projId ");
			Query query2 = session.createSQLQuery(sql1.toString());
			query2.setString("projId", data.getProjId());
			query2.executeUpdate();
			session.flush();
		}
	}
	
	/**
	 * @category 保存规则医疗机构
	 * @author 徐国鹏
	 * @return RuleHospType
	 */
	@Override
	public void saveHospLevelList(BidExpertExtractRuleListForm data) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		if(data.getType()==1){
			List<Map<String,Object>> list = data.getHospTypeList();
			StringBuffer sql1 = new StringBuffer();
			if("delete".equals(data.gethFlag())){
				sql1.append("delete from rule_hosp_type where rule_id = :ruleId and rule_hosp_type_id not in (");
				for(int i = 0;i<list.size();i++){
					sql1.append(":ruleHospTypeId"+i);
					if(i<list.size()-1){
						sql1.append(" ,  ");
					}
				}
				sql1.append(")");
				Query query = session.createSQLQuery(sql1.toString());
				for(int i = 0;i<list.size();i++){
					query.setString("ruleHospTypeId"+i, list.get(i).get("ruleHospTypeId").toString());
				}
				query.setString("ruleId", data.getRuleId());
				query.executeUpdate();
				session.flush();
			}else if("insert".equals(data.gethFlag())){
				sql1.append("insert into rule_hosp_type select t.* from ( ");
				for(int i = 0;i<list.size();i++){
					sql1.append(" select :ruleHospTypeId"+i+" rule_hosp_type_id,:ruleId rule_id,:hospTypeId"+i+" hosp_type_id,:hospTypePer"+i+" expert_rate ");
					if(i<list.size()-1){
						sql1.append(" UNION  ");
					}
				}
				sql1.append(" ) t  where t.rule_hosp_type_id not in (select rule_hosp_type_id from rule_hosp_type where rule_id = :ruleId) ");
				Query query = session.createSQLQuery(sql1.toString());
				List<String> idList = new ArrayList<String>();
				for(int i = 0;i<list.size();i++){
					String uuid = UUID.randomUUID().toString();
					if(StringUtils.isNotBlank(list.get(i).get("ruleHospTypeId").toString())){
						query.setString("ruleHospTypeId"+i, list.get(i).get("ruleHospTypeId").toString());
					}else{
						idList.add(uuid);
						query.setString("ruleHospTypeId"+i, uuid);
					}
					
					query.setString("hospTypeId"+i, list.get(i).get("hospTypeId").toString());
					query.setString("hospTypePer"+i, list.get(i).get("hospTypePer").toString());
				}
				query.setString("ruleId", data.getRuleId());
				query.executeUpdate();
				session.flush();
				writeHLog(idList,data.getLogId());
			}
			
			//update
			sql.append("update rule_hosp_type m inner join ( ");
			
			for(int i = 0;i<list.size();i++){
				sql.append(" select :ruleHospTypeId"+i+" a,:hospTypeId"+i+" b,:hospTypePer"+i+" c ");
				if(i<list.size()-1){
					sql.append(" UNION  ");
				}
			}
			sql.append(" ) n on n.a = m.rule_hosp_type_id set m.hosp_type_id = n.b,m.expert_rate = n.c ");
			
			Query query = session.createSQLQuery(sql.toString());
			List<String> idList = new ArrayList<String>();
			for(int i = 0;i<list.size();i++){
				idList.add(list.get(i).get("ruleHospTypeId").toString());
				query.setString("ruleHospTypeId"+i, list.get(i).get("ruleHospTypeId").toString());
				query.setString("hospTypeId"+i, list.get(i).get("hospTypeId").toString());
				query.setString("hospTypePer"+i, list.get(i).get("hospTypePer").toString());
			}
			query.executeUpdate();
			session.flush();
			writeHLog(idList,data.getLogId());
		}else{
			//insert
			sql.append("insert into rule_hosp_type select t.* from ( ");
			List<Map<String,Object>> list = data.getHospTypeList();
			for(int i = 0;i<list.size();i++){
				sql.append(" select :ruleHospTypeId"+i+" rule_hosp_type_id,:ruleId"+i+" rule_id,:hospTypeId"+i+" hosp_type_id,:hospTypePer"+i+" expert_rate ");
				if(i<list.size()-1){
					sql.append(" UNION  ");
				}
			}
			sql.append(" ) t");
			Query query = session.createSQLQuery(sql.toString());
			List<String> idList = new ArrayList<String>();
			for(int i = 0;i<list.size();i++){
				String uuid = UUID.randomUUID().toString();
				idList.add(uuid);
				query.setString("ruleId"+i, data.getRuleId());
				query.setString("ruleHospTypeId"+i, uuid);
				query.setString("hospTypeId"+i, list.get(i).get("hospTypeId").toString());
				query.setString("hospTypePer"+i, list.get(i).get("hospTypePer").toString());
			}
			query.executeUpdate();
			session.flush();
			writeHLog(idList,data.getLogId());
		}
		
	}
	
	/**
	 * @category 写医疗机构类型日志
	 * @author 徐国鹏
	 * @return RuleHospType
	 */
	public void writeHLog(List<String> idList,String logId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("insert into rule_hosp_type_log select uuid(),t.*,:logId from rule_hosp_type t where t.rule_hosp_type_id in (:idList) ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("logId", logId);
		query.setParameterList("idList", idList);
		query.executeUpdate();
		session.flush();
	}
	/**
	 * @category 保存规则专家类型
	 * @author 徐国鹏
	 * @return RuleHospType
	 */
	@Override
	public void saveExpertLevelList(BidExpertExtractRuleListForm data) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		if(data.getType()==1){
			List<Map<String,Object>> list = data.getExpertTypeList();
			StringBuffer sql1 = new StringBuffer();
			if("delete".equals(data.geteFlag())){
				sql1.append("delete from bid_expert_type where rule_id = :ruleId and bid_expert_type_id not in (");
				for(int i = 0;i<list.size();i++){
					sql1.append(":bidExpertTypeId"+i);
					if(i<list.size()-1){
						sql1.append(" ,  ");
					}
				}
				sql1.append(")");
				Query query = session.createSQLQuery(sql1.toString());
				for(int i = 0;i<list.size();i++){
					query.setString("bidExpertTypeId"+i, list.get(i).get("bidExpertTypeId").toString());
				}
				query.setString("ruleId", data.getRuleId());
				query.executeUpdate();
				session.flush();
			}else if("insert".equals(data.geteFlag())){
				sql1.append("insert into bid_expert_type select t.* from ( ");
				for(int i = 0;i<list.size();i++){
					sql1.append(" select :bidExpertTypeId"+i+" bid_expert_type_id,:ruleId rule_id,:expertTypeId"+i+" expert_type_id,:expertTypeNum"+i+" count ");
					if(i<list.size()-1){
						sql1.append(" UNION  ");
					}
				}
				sql1.append(" ) t  where t.bid_expert_type_id not in (select bid_expert_type_id from bid_expert_type where rule_id = :ruleId) ");
				Query query = session.createSQLQuery(sql1.toString());
				List<String> idList = new ArrayList<String>();
				for(int i = 0;i<list.size();i++){
					String uuid = UUID.randomUUID().toString();
					
					if(StringUtils.isNotBlank(list.get(i).get("bidExpertTypeId").toString())){
						query.setString("bidExpertTypeId"+i, list.get(i).get("bidExpertTypeId").toString());
					}else{
						query.setString("bidExpertTypeId"+i, uuid);
						idList.add(uuid);
					}
					
					query.setString("expertTypeId"+i, list.get(i).get("expertTypeId").toString());
					query.setString("expertTypeNum"+i, list.get(i).get("expertTypeNum").toString());
				}
				query.setString("ruleId", data.getRuleId());
				query.executeUpdate();
				session.flush();
				writeELog(idList,data.getLogId());
			}
			//update
			sql.append("update bid_expert_type m inner join ( ");
			
			for(int i = 0;i<list.size();i++){
				sql.append(" select :bidExpertTypeId"+i+" a,:expertTypeId"+i+" b,:expertTypeNum"+i+" c ");
				if(i<list.size()-1){
					sql.append(" UNION  ");
				}
			}
			sql.append(" ) n on n.a = m.bid_expert_type_id set m.expert_type_id = n.b,m.count = n.c ");
			
			Query query = session.createSQLQuery(sql.toString());
			List<String> idList = new ArrayList<String>();
			for(int i = 0;i<list.size();i++){
				idList.add(list.get(i).get("bidExpertTypeId").toString());
				query.setString("bidExpertTypeId"+i, list.get(i).get("bidExpertTypeId").toString());
				query.setString("expertTypeId"+i, list.get(i).get("expertTypeId").toString());
				query.setString("expertTypeNum"+i, list.get(i).get("expertTypeNum").toString());
			}
			query.executeUpdate();
			session.flush();
			writeELog(idList,data.getLogId());
		}else{
			//insert
			sql.append("insert into bid_expert_type select t.* from ( ");
			List<Map<String,Object>> list = data.getExpertTypeList();
			for(int i = 0;i<list.size();i++){
				sql.append(" select :bidExpertTypeId"+i+" bid_expert_type_id,:ruleId rule_id,:expertTypeId"+i+" expert_type_id,:expertTypeNum"+i+" count ");
				if(i<list.size()-1){
					sql.append(" UNION  ");
				}
			}
			sql.append(" ) t");
			Query query = session.createSQLQuery(sql.toString());
			List<String> idList = new ArrayList<String>();
			for(int i = 0;i<list.size();i++){
				String uuid = UUID.randomUUID().toString();
				idList.add(uuid);
				query.setString("bidExpertTypeId"+i, uuid);
				query.setString("expertTypeId"+i, list.get(i).get("expertTypeId").toString());
				query.setString("expertTypeNum"+i, list.get(i).get("expertTypeNum").toString());
			}
			query.setString("ruleId", data.getRuleId());
			query.executeUpdate();
			session.flush();
			writeELog(idList,data.getLogId());
		}
		
	}
	
	/**
	 * @category 写专家类型类型日志
	 * @author 徐国鹏
	 * @return RuleHospType
	 */
	public void writeELog(List<String> idList,String logId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("insert into bid_expert_type_log select uuid(),t.*,:logId from bid_expert_type t where t.bid_expert_type_id in (:idList) ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("logId", logId);
		query.setParameterList("idList", idList);
		query.executeUpdate();
		session.flush();
	}
	
	/**
	 * @category 抽取规则专家分类
	 * @author 徐国鹏
	 * @return RuleHospType
	 */
	@Override
	public BidExpertType getBidExpertType(String ruleId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from dic_experttype ");
		Query query = session.createSQLQuery(sql.toString()).addEntity(BidExpertType.class);
		query.setParameter("ruleId", ruleId);
		return (BidExpertType) query.list();
	}
	
	/** 
	 * @category 获取一级专家类别列表
	 * @author 徐国鹏
	 * @param fatherRuleId
	 * @return List<BidExpertExtractRule> list
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<BidExpertExtractListRuleList> getSuExpertTypeByFatherId(String fatherRuleId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.* FROM dic_experttype A WHERE A.father_experttype_id = :fatherRuleId");
		Query query = session.createSQLQuery(sql.toString()).addEntity(DicExperttype.class);
		query.setParameter("fatherRuleId", fatherRuleId);
		return query.list();
	}
	
	/**
	 * @category 抽取规则设置--获取规则列表
	 * @author 徐国鹏
	 * @param Pagination page 
	 * @return BidProjExpert
	 */
	@Override
	public Pagination getExpertExtractRulePage(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.rule_id as ruleId,a.rule_code as ruleCode,a.infusion as infusion,a.exec_status as execStatus,a.proj_id as projId, ");
		sql.append(" DATE_FORMAT(a.exec_datetime,'%Y-%m-%d %T') as execDatetime,a.extract_num as extractNum,a.status as status ");
		sql.append(" from bid_expert_extract_rule a ");
		sql.append(" where 1=1 ");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNoneBlank(page.getConditions().get("projId"))){
			sql.append(" and a.proj_id = :projId");
			params.put("projId", page.getConditions().get("projId"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("infusion"))){
			sql.append(" and a.infusion = :infusion");
			params.put("infusion", page.getConditions().get("infusion"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("execStatus"))){
			sql.append(" and a.exec_status = :execStatus");
			params.put("execStatus", page.getConditions().get("execStatus"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("extract_num"))){
			sql.append(" and a.extract_num = :extract_num");
			params.put("extract_num", page.getConditions().get("extract_num"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("status"))){
			sql.append(" AND a.status = :status");
			params.put("status", page.getConditions().get("status"));
		}
		sql.append(" order by a.exec_datetime DESC ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
		
	}
	
	/**
	 * @category 抽取规则设置--医疗机构分类（子表）
	 * @author 徐国鹏
	 * @param Pagination page
	 * @return BidProjExpert
	 */
	@Override
	public Pagination getHospTypeList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.expert_rate as expertRate ,b.dic_name as dicName  ");
		sql.append(" from rule_hosp_type a ");
		sql.append(" left join dic_sysdic b on b.classify_code='113' and b.dic_code=a.hosp_type_id  ");
		sql.append(" where rule_id= :ruleId ");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNoneBlank(page.getConditions().get("ruleId"))){
			sql.append(" and a.rule_id = :ruleId");
			params.put("ruleId", page.getConditions().get("ruleId"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("expertRate"))){
			sql.append(" and a.expert_rate = :expertRate");
			params.put("expertRate", page.getConditions().get("expertRate"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("dicName"))){
			sql.append(" and b.dic_name = :dicName");
			params.put("dicName", page.getConditions().get("dicName"));
		}
		page.setRows(dao.getSqlMapList(sql.toString(), params));
		return  page;
		
	}
	
	
	/**
	 * @category 抽取专家查看--获取专家查看
	 * @author 徐国鹏
	 * @param Pagination page
	 * @return BidProjExpert
	 */
	@Override
	public Pagination getExpertExtractRulePageList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.rule_id as ruleId,a.infusion as infusion,a.exec_status as execStatus,a.proj_id as projId, ");
		sql.append(" DATE_FORMAT(a.exec_datetime,'%Y-%m-%d %T') as execDatetime,a.extract_num as extractNum,a.status as status ");
		sql.append(" from bid_expert_extract_rule a ");
		sql.append(" where 1=1 ");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNoneBlank(page.getConditions().get("projId"))){
			sql.append(" and a.proj_id = :projId");
			params.put("projId", page.getConditions().get("projId"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("infusion"))){
			sql.append(" and a.infusion = :infusion");
			params.put("infusion", page.getConditions().get("infusion"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("execStatus"))){
			sql.append(" and a.exec_status = :execStatus");
			params.put("execStatus", page.getConditions().get("execStatus"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("extract_num"))){
			sql.append(" and a.extract_num = :extract_num");
			params.put("extract_num", page.getConditions().get("extract_num"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("status"))){
			sql.append(" AND a.status = :status");
			params.put("status", page.getConditions().get("status"));
		}
		sql.append(" order by a.exec_datetime DESC ");
		page.setRows(dao.getSqlMapList(sql.toString(), params));
		return page;
		
	}
	
	/**
	 * @category 抽取专家查看--专家查看（子表）
	 * @author 徐国鹏
	 * @param Pagination page
	 * @return BidProjExpert
	 */
	@Override
	public Pagination getExpertTypeRuleList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select b.name as name,c.expert_type_name as expertTypeName,d.dic_name as dicName,a.group_num as groupNum from bid_projexpert a  ");
		sql.append(" left join std_expert b on a.expert_id=b.expert_id ");
		sql.append(" left join dic_experttype c on a.expert_type=c.dic_experttype_id ");
		sql.append(" left join dic_sysdic d on d.classify_code='113' and d.dic_code=a.hosp_type ");
		sql.append(" where a.rule_id= :ruleId ");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNoneBlank(page.getConditions().get("ruleId"))){
			sql.append(" and a.rule_id = :ruleId");
			params.put("ruleId", page.getConditions().get("ruleId"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("count"))){
			sql.append(" and a.count = :count");
			params.put("count", page.getConditions().get("count"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("expertTypeName"))){
			sql.append(" and b.expert_type_name = :expertTypeName");
			params.put("expertTypeName", page.getConditions().get("expertTypeName"));
		}
		
		page.setRows(dao.getSqlMapList(sql.toString(), params));
		return  page;
		
	}
	/**
	 * @category 抽取专家查看--专家查看（子表）
	 * @author 徐国鹏
	 * @param Pagination page
	 * @return BidProjExpert
	 */
	@Override
	public Pagination getExperttypeRuleList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.count as count ,b.expert_type_name as expertTypeName   ");
		sql.append(" from bid_expert_type a ");
		sql.append(" left join dic_experttype b on b.dic_experttype_id=a.expert_type_id  ");
		sql.append(" where a.rule_id= :ruleId ");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNoneBlank(page.getConditions().get("ruleId"))){
			sql.append(" and a.rule_id = :ruleId");
			params.put("ruleId", page.getConditions().get("ruleId"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("count"))){
			sql.append(" and a.count = :count");
			params.put("count", page.getConditions().get("count"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("expertTypeName"))){
			sql.append(" and b.expert_type_name = :expertTypeName");
			params.put("expertTypeName", page.getConditions().get("expertTypeName"));
		}
		
		page.setRows(dao.getSqlMapList(sql.toString(), params));
		return  page;
		
	}
	
	/**
	 * 
	 * @category 已用、停用
	 * @Auther 徐国鹏
	 * @date 2015年10月15日 上午12:16:59
	 * @param data
	 * @return void
	 */
	public void switchStatus(BidExpertExtractRuleListForm data) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("update bid_expert_extract_rule  set ")
		  .append(" status=:status,upd_datetime=:updDateTime,upd_user=:updUser ")
		  .append(" where rule_id= :ruleId ");
		Query update = session.createSQLQuery(sql.toString());
		update.setLong("status", data.getStatus()); 
		update.setTimestamp("updDateTime", new Date());
		update.setString("updUser", data.getUpdUser());
		update.setString("ruleId", data.getRuleId());
		update.executeUpdate();
		session.flush();
		writeLog(data);
		writeLog2(data);
		writeLog3(data);
	}
	/**
	 * 
	 *@category 写日志
	 *@date 2015年6月9日
	 *Pagination
	 */
	@Override
	public void writeLog(BidExpertExtractRuleListForm data){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into bid_expert_extract_rule_log(log_id,rule_id,proj_id,rule_code,max_extract_num,  ")
		   .append(" infusion,exec_status,exec_user,exec_datetime,extract_num,status,remarks,upd_datetime,upd_user) select ")
		   .append(" :logId,t.* from bid_expert_extract_rule t where t.rule_id= :ruleId ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("ruleId",data.getRuleId());
		query.setString("logId", data.getLogId());
		query.executeUpdate();
		session.flush();
	}
	@Override
	public void writeLog2(BidExpertExtractRuleListForm data){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into bid_expert_type_log(log_id,bid_expert_type_id,rule_id,expert_type_id,count,rule_log_id)  ")
		   .append(" select UUID(),t.*,:logId from bid_expert_type t where t.rule_id= :ruleId ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("ruleId",data.getRuleId());
		query.setString("logId", data.getLogId());
		query.executeUpdate();
		session.flush();
	}
	@Override
	public void writeLog3(BidExpertExtractRuleListForm data){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into rule_hosp_type_log(log_id,rule_hosp_type_id,rule_id,hosp_type_id,expert_rate,rule_log_id)  ")
		   .append(" select UUID(),t.*,:logId from rule_hosp_type t where t.rule_id= :ruleId ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("ruleId",data.getRuleId());
		query.setString("logId", data.getLogId());
		query.executeUpdate();
		session.flush();
		
	}
	
		/**
		* @category 保存规则日志
	    * @author zhouqi
		* @param Pagination page
		* @return Pagination
		*/
		@Override
		public Boolean saveRuleLog(BidExpertExtractRuleListFormLog bidExpertExtractRuleListFormLog) {
			try{
				Session session = sessionFactory.getCurrentSession();
				session.saveOrUpdate(bidExpertExtractRuleListFormLog);
			}catch(Exception e){
				e.printStackTrace();
				return false;
			}
			return true;
		}
	/**
	 * @category 根据类别Id获取专家类别
	 * @author 徐国鹏
	 * @param fatherRuleId
	 * @return List<DicExperttype> list
	 */
	@Override
	public DicExperttype getDicExperttypeById(String id) {
		Session session = sessionFactory.getCurrentSession();
		DicExperttype dicExpertType = (DicExperttype) session.get(DicExperttype.class,id);
		return dicExpertType;
	}
	/**
	 * @category 根据类别Id获取专家类别（日志详情）
	 * @author 徐国鹏
	 * @param fatherRuleId
	 * @return List<DicExperttype> list
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getExpertName(String id) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.count as count,b.expert_type_name as expertTypeName from bid_expert_type_log a ");
		sql.append(" left join dic_experttype b on b.dic_experttype_id=a.expert_type_id  ");
		sql.append(" where a.rule_log_id= :logId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("logId", id);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return  (List<Map<String, Object>>) query.list();
	}
	/**
	 * @category 根据类别Id获取医疗机构类别（日志详情）
	 * @author 徐国鹏
	 * @param fatherRuleId
	 * @return List<DicExperttype> list
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getHospType(String id) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.expert_rate,c.dic_name from rule_hosp_type_log a  ");
		sql.append(" left join dic_sysdic c on c.classify_code='113' and c.dic_code=a.hosp_type_id  ");
		sql.append(" where a.rule_log_id = :logId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("logId", id);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return  (List<Map<String, Object>>) query.list();
	}
	
	/**
	 * @category 根据类别Id获取专家类别
	 * @author 徐国鹏
	 * @param fatherRuleId
	 * @return List<DicExperttype> list
	 */
	@Override
	public BidExpertExtractRuleListForm getDicExpertTypeById(String id) {
		Session session = sessionFactory.getCurrentSession();
		BidExpertExtractRuleListForm dicExpertType = (BidExpertExtractRuleListForm) session.get(BidExpertExtractRuleListForm.class,id);
		return dicExpertType;
	}
	
	/**
	 * @category 根据类别Id获取医疗机构分类
	 * @author 徐国鹏
	 * @param fatherRuleId
	 * @return List<DicExperttype> list
	 */
	public DicSys getHospTypeById(String id) {
		Session session = sessionFactory.getCurrentSession();
		DicSys dicExpertType = (DicSys) session.get(DicSys.class,id);
		return dicExpertType;
	}
	
	
	/**
	* @category 获取抽取规则维护记录前后对比
	* @author zhouqi
	* @param Pagination page
	* @return Pagination
	*/
	
	@SuppressWarnings("unchecked")
	@Override
	public List<BidExpertExtractRuleListFormLog> getRuleCompare(String ruleId,Timestamp timestamp) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT a.log_id,a.rule_id , a.proj_id,a.rule_code ,a.max_extract_num ,a.infusion , ");
		sql.append(" a.exec_status ,s.name ,DATE_FORMAT(a.exec_datetime,'%Y-%m-%d %T') exec_datetime,a.exec_user, ");
		sql.append(" a.extract_num ,a.status ,a.remarks ,DATE_FORMAT(a.upd_datetime,'%Y-%m-%d %T')  upd_datetime,s.name upd_user  ");
		sql.append(" FROM bid_expert_extract_rule_log a  ");
		sql.append(" left join sys_user s on s.id=a.upd_user  ");
		sql.append(" WHERE 1= 1 ");
		sql.append(" and a.rule_id = :ruleId  and a.upd_datetime <= :timestamp ");
		sql.append(" order by a.upd_datetime DESC limit 2 ");
		Query query = session.createSQLQuery(sql.toString()).addEntity(BidExpertExtractRuleListFormLog.class);
		query.setParameter("ruleId", ruleId);
		query.setTimestamp("timestamp", timestamp);
		List<BidExpertExtractRuleListFormLog> list = query.list();
		return list;
	}
	
	
	/**
	 * @category 保存项目专家关联
	 * @author zhouqi
	 * @param BidProjExpert bidProjExpert
	 * @return Boolean
	 */
	@Override
	public Boolean savaHospExpert(BidProjExpert bidProjExpert) {
		try{
			Session session = sessionFactory.getCurrentSession();
			session.saveOrUpdate(bidProjExpert);
			session.flush();
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
	//////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	
	
	
    
	/**
	 * @category 保存抽取规则
	 * @author zhouqi
	 * @param Pagination page
	 * @return BidProjExpert
	 */
	@Override
	public Boolean saveExtractRule(BidExpertExtractRuleListForm extractRule) {
		try{
			Session session = sessionFactory.getCurrentSession();
			session.saveOrUpdate(extractRule);
			session.flush();
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
    
	
	/**
	 * @category 执行抽取规则
	 * @author zhouqi
	 * @param Pagination page
	 * @return BidProjExpert
	 */
	@Override
	public List<BidProjExpert>  executeExtractRule(List<BidExpertExtractRuleListForm> list) {
		List<BidProjExpert> bidProjExpertList = expertExtract.extractExpert(list);
		return bidProjExpertList;
	}
	/**
	 * @category 根据id抽取规则
	 * @author zhouqi
	 * @param String
	 * @return BidExpertExtractRule
	 */
	@Override
	public BidExpertExtractRule getRuleById(String ruleId) {
		Session session = sessionFactory.getCurrentSession();
		BidExpertExtractRule bidExpertExtractRule = (BidExpertExtractRule) session.get(BidExpertExtractRule.class, ruleId);
		session.flush();
		session.clear();
		return bidExpertExtractRule;
	}
	
	/**
	 * @category 根据父类id抽取所有子类id
	 * @author zhouqi
	 * @param String
	 * @return BidExpertExtractRule
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<BidExpertExtractRule> getRuleByFatherRuleId(String fatherRuleId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("select A.* from bid_expert_extract_rule A where A.father_rule_id = :fatherRuleId");
		Query query = session.createSQLQuery(sql.toString());
		query.setParameter("fatherRuleId", fatherRuleId);
		return query.list();
	}
   
	
	
	
	
	/**
	 * @category 保存项目专家关联
	 * @author zhouqi
	 * @param BidProjExpert bidProjExpert
	 * @return Boolean
	 */
	@Override
	public Boolean saveProjExpert(BidProjExpert bidProjExpert) {
		try{
			Session session = sessionFactory.getCurrentSession();
			session.saveOrUpdate(bidProjExpert);
			session.flush();
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
	   /**
		 * @category 获取被抽取专家列表
		 * @author zhouqi
		 * @param Pagination page
		 * @return Pagination
		 */
	@Override
	public Pagination getExtractExpertList(Pagination page) {
		@SuppressWarnings("unused")
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT E.proj_id as projId,C.rule_id as ruleId, E.`name` as projName,IFNULL(F.id,0) as allocStatus,  A.`name` as expertName, D.expert_type_name AS expertTypeName, A.expert_id as expertId,");
		sql.append("  A.telephone as handPhone, A.sex, date_format(A.birthday,'%Y-%m-%d') as birthday, A.position as position, A.company_id as companyId FROM std_expert A");
		sql.append(" LEFT JOIN bid_projexpert B ON A.expert_id = B.expert_id");
		sql.append(" LEFT JOIN bid_expert_extract_rule C ON B.rule_id = C.rule_id");
		sql.append(" LEFT JOIN dic_experttype D ON D.dic_experttype_id = C.expert_type_id");
		sql.append(" LEFT JOIN bid_project E ON E.proj_id = B.proj_id  ");
		sql.append("   LEFT JOIN sys_user F ON F.id = A.expert_id WHERE 1 = 1");
		sql.append(" AND A.`status` = 1 AND B.`status` = 1 AND C.`status` = 1 AND D.`status` = 1 AND E.`status` = 1");
		Map<String,Object> params = new HashMap<String, Object>();
		if(StringUtils.isNoneBlank(page.getConditions().get("projId"))){
			sql.append(" AND B.proj_id = :projId ");
			params.put("projId", page.getConditions().get("projId"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("ruleId"))){
			sql.append(" AND B.rule_id = :ruleId");
			params.put("ruleId", page.getConditions().get("ruleId"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("name"))){
			sql.append(" AND A.`name` like :name");
			params.put("name", "%"+page.getConditions().get("name")+"%");
		}
	
		return hibernateUtil.getSqlPageMapList(sql.toString(), params, page);
	}
	
   /**
	* @category 根据专家类别获取专家数量
    * @author zhouqi
	* @param Pagination page
	* @return Pagination
	*/
	@Override
	public Integer getExpertCountByType(String expertTypeId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT count(*) FROM std_expert A LEFT JOIN dic_exper_ettype B ON A.expert_id = B.expert_id");
		sql.append(" LEFT JOIN dic_experttype C ON B.expert_type_id = C.dic_experttype_id ");
		sql.append(" WHERE C.dic_experttype_id = :expertTypeId");
		Query query = session.createSQLQuery(sql.toString());
		query.setParameter("expertTypeId", expertTypeId);
		BigInteger count = (BigInteger) query.uniqueResult();
		return count.intValue();
	}
	
	 /**
		* @category 根据专家类型id获取下面所有专家数量
	    * @author zhouqi
		* @param Pagination page
		* @return Pagination
		*/
	@Override
	public Integer getAllSubExpertCountByTypeId(String expertTypeId) {
		List<DicExperttype> List = getAllSubExpertType(expertTypeId);
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT A.expert_id FROM std_expert A LEFT JOIN dic_exper_ettype B ON A.expert_id = B.expert_id");
		sql.append(" LEFT JOIN dic_experttype C ON B.expert_type_id = C.dic_experttype_id ");
		sql.append(" WHERE C.dic_experttype_id in(");
		for(int i =0; i < List.size();i++){
			sql.append("'"+List.get(i).getDicExperttypeId()+"',");
		}
		sql.append("'')");
		Query query = session.createSQLQuery(sql.toString());
		Integer count = query.list().size();
		return count;
	}
	 
	 /**
	* @category 获取抽取规则日志列表
    * @author zhouqi
	* @param Pagination page
	* @return Pagination
	*/
	@Override
	public Pagination getExtractRuleLogList(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.log_id as logId,a.rule_id as ruleId, a.proj_id as projId,a.max_extract_num as maxExtractNum,a.infusion as infusion, ");
		sql.append(" a.exec_status as execStatus,s.name as execUser,DATE_FORMAT(a.exec_datetime,'%Y-%m-%d %T') as execDatetime,a.extract_num as extractNum, ");
		sql.append(" a.status as status,a.remarks as remarks,DATE_FORMAT(a.upd_datetime,'%Y-%m-%d %T') as updDatetime ,s.name as updUser ");
		sql.append(" from bid_expert_extract_rule_log a ");
		sql.append(" left join sys_user s on s.id=a.upd_user ");
		sql.append(" where a.rule_id = :ruleId ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("ruleId", page.getConditions().get("ruleId"));
		if(StringUtils.isNoneBlank(page.getConditions().get("startPoint"))){
			DateUtil.StringTime2Timestamp(page.getConditions().get("startPoint"));
			sql.append(" and a.upd_datetime >= :startPoint");
			params.put("startPoint", page.getConditions().get("startPoint"));
		}
		if(StringUtils.isNoneBlank(page.getConditions().get("endPoint"))){
			sql.append(" and a.upd_datetime < :endPoint");
			params.put("endPoint", page.getConditions().get("endPoint"));
		}
		sql.append(" order by a.upd_datetime DESC ");
		page = hibernateUtil.getSqlPageMapList(sql.toString(), params, page);
		return page;
	}
    
	
	/**
	 * @category 根据id获取专家类别下级类别
	 * @param list
	 * @return List<BidExpertExtractRule>
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicExperttype> getSubTypeByid(String id){
		Session session = sessionFactory.getCurrentSession();
	    StringBuffer sql = new StringBuffer();
	    sql.append("select * from dic_experttype A where A.father_experttype_id = :id");
	    Query query = session.createSQLQuery(sql.toString()).addEntity(DicExperttype.class);
	    query.setParameter("id", id);
	    return query.list();
	    
	}
	
	/**
	 * @category 根据类型id获取类型所处的级别
	 * @param experttypId
	 * @return
	 */
	@Override
	public int getLevelOfExperttype(String experttypId){
		int i=1;
		String fatherExpertId = experttypId;
		while(true){
			fatherExpertId = getDicExperttypeById(fatherExpertId).getFatherExperttypeId();
			if(!fatherExpertId.equals("0")){
				i++;
			}else{
				break;
			}
		}
		return i;
	}
	/**
	 * @category 获取一个类别的所有子类
	 * @param id
	 * @return
	 */
	@Override
	public List<DicExperttype> getAllSubExpertType(String id){
		List<DicExperttype> list = new ArrayList<DicExperttype>();
		list.add(getDicExperttypeById(id));
		for(int i = 0; i< list.size();i++){
			List<DicExperttype> tempList = this.getSubTypeByid(list.get(i).getDicExperttypeId());
			if(tempList.size() < 0){
				break;
			}
			list.addAll(tempList);
		}
		return list;
	}
	
	/**
	 * @category 根据项目id获取所有专家
	 * @param String projId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<StdExpert> getExpertByProjId(String id){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.* FROM std_expert A");
		sql.append(" LEFT JOIN bid_projexpert B ON A.expert_id = B.expert_id");
		sql.append(" LEFT JOIN bid_expert_extract_rule C ON B.rule_id = C.rule_id");
		sql.append(" LEFT JOIN dic_experttype D ON D.dic_experttype_id = C.expert_type_id");
		sql.append(" LEFT JOIN bid_project E ON E.proj_id = B.proj_id  ");
		sql.append("   LEFT JOIN sys_user F ON F.id = A.expert_id WHERE 1 = 1");
		sql.append(" AND A.`status` = 1 AND B.`status` = 1 AND C.`status` = 1 AND D.`status` = 1 AND E.`status` = 1");
		if(StringUtils.isNoneBlank(id)){
			sql.append(" AND B.proj_id = :projId ");
		}
		Query query = session.createSQLQuery(sql.toString()).addEntity(StdExpert.class);
		query.setParameter("projId", id);
		return query.list();
		
	}
	
	
	
	

}
